begin transaction SchemaUpdate
print 'transaction "SchemaUpdate" started'

begin try
	
	create table [Conference]
	(
		Id uniqueidentifier not null primary key,
		Name nvarchar(50) not null
	)
	print 'table "Conference" created'
	
	exec sp_rename [Meeting], [Group]
	print 'table "Meeting" renamed to "Group"'
	
	alter table [Group]
	add [ConferenceId] uniqueidentifier null
	print 'ConferenceId added to "Group"'
	
	alter table [Group]
	add constraint FK_Group_Conference foreign key (ConferenceId) references [Conference](Id)
	print 'Constraint FK_Group_Conference added to "Group"'
	
	create table [Game]
	(
		Id uniqueidentifier not null primary key,
		GroupId uniqueidentifier not null,
		Number int not null
	)
	print 'table "Game" created'
	
	alter table [Game]
	add constraint FK_Game_Group foreign key (GroupId) references [Group](Id)
	print 'Constraint FK_Game_Group added to "Game"'
	
	alter table [SessionUser]
	add GroupId uniqueidentifier not null
	print 'GroupId added to "SessionUser"'

	alter table [SessionUser]
	drop constraint FK_SessionUser_Meeting
	print 'Constraint FK_SessionUser_Meeting dropped from "SessionUser"'

	alter table [SessionUser]
	drop column MeetingId
	print 'MeetingId removed from "SessionUser"'

	alter table [SessionUser]
	add constraint FK_SessionUser_Group foreign key (GroupId) references [Group](Id)
	print 'Constraint FK_SessionUser_Group added to "SessionUser"'

	create table [BingoSet]
	(
		Id uniqueidentifier primary key not null,
		SessionUserId uniqueidentifier not null,
		GameId uniqueidentifier not null
	)
	print 'table "BingoSet" created'

	alter table [BingoSet]
	add
		constraint FK_BingoSet_SessionUser foreign key (SessionUserId) references [SessionUser](Id),
		constraint FK_BingoSet_Game foreign key (GameId) references [Game](Id)
	print 'Constraints FK_BingoSet_SessionUser and FK_BingoSet_Game added to "BingoSet"'

	alter table [BingoWordSet]
	add BingoSetId uniqueidentifier not null
	print 'BingoSetId added to "BingoWordSet"'
	
	alter table [BingoWordSet]
	add constraint FK_BingoWordSet_BingoSet foreign key (BingoSetId) references [BingoSet](Id)
	print 'Constraint FK_BingoWordSet_BingoSet added to "BingoWordSet"'
	
	alter table [BingoWordSet]
	drop constraint FK_BingoWordSet_SessionUser
	print 'Constraint FK_BingoWordSet_SessionUser dropped from "BingoWordSet"'

	alter table [BingoWordSet]
	drop column SessionUserId
	print 'column SessionUserId dropped from "BingoWordSet"'

	commit transaction SchemaUpdate
	print 'transaction "SchemaUpdate" committed'		
end try
begin catch	
	rollback transaction SchemaUpdate
	print 'transaction "SchemaUpdate" rolled back'		
end catch
go
			
begin transaction DataUpdate
begin try
	
	update [Version]
	set VersionNr = '1.5'
	print 'VersionNr updated to 1.5'
	
	commit transaction DataUpdate
	print 'transaction "DataUpdate" committed'
end try
begin catch
	rollback transaction DataUpdate
	print 'transaction "DataUpdate" rolled back'
end catch